package com.sincereaddon.dao;

import com.sincereaddon.model.Device;
import com.sincereaddon.model.Passengerflow;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

/**
 * Created by xjw on 2018/1/9.
 */
@Repository
public class IretailerDao {

    @Autowired
    @Qualifier("primaryJdbcTemplate")
    private JdbcTemplate jdbcTemplateMySql;


//    public List<Passengerflow> getPassengerflow(String startDateTime,String endDateTime){
//        String sql = "SELECT * FROM passengerflow WHERE DateTime >= ? AND DateTime<= ?";
//        Object[] args = {startDateTime,endDateTime};
//        List<Passengerflow> resutl = jdbcTemplateMySql.query(sql,args,new BeanPropertyRowMapper(Passengerflow.class));
//        return  resutl;
//    }

    public List<Device> getAllDevice(Integer zone){

        String sql = "SELECT d.*,z.ID AS zoneId FROM device d INNER JOIN zone z ON d.ID = z.DeviceID AND z.DeviceZoneID= ?";
        Object[] args = {zone};
        List<Device> result = jdbcTemplateMySql.query(sql,args,new BeanPropertyRowMapper<>(Device.class));
        return result;
    }

    public int updateDevice(String deviceNameId, String updateTime){
        String sql = "UPDATE device SET UpdateTime = ? WHERE DeviceNameID = ? ";
        Object[] args = {updateTime,deviceNameId};
        int result = jdbcTemplateMySql.update(sql,args);

        return result;
    }

    public int batchReplacePassengerflow(final List<Passengerflow> post) {

        String sql = " INSERT INTO passengerflow(ZoneID, DateTime, Date, DateHour, DateMin, CountEnter, CountExit) values(?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE\n" +
                "ZoneID=VALUES(ZoneID),DateTime=VALUES(DateTime),Date=VALUES(Date) ,DateHour=VALUES(DateHour) ,DateMin=VALUES(DateMin) ,CountEnter=VALUES(CountEnter) ,CountExit=VALUES(CountExit);";

        int[] results = jdbcTemplateMySql.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setInt(1,  post.get(i).getZoneId());
                ps.setString(2,  post.get(i).getDateTime());
                ps.setString(3,  post.get(i).getDate());
                ps.setInt(4,  post.get(i).getDateHour());
                ps.setInt(5,  post.get(i).getDateMin());
                ps.setInt(6,  post.get(i).getCountEnter());
                ps.setInt(7,  post.get(i).getCountExit());

            }

            @Override
            public int getBatchSize() {
                return post.size();
            }
        });

        return results.length;
    }


}
